﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace UFIDA.U8.YYNBJY.DBHelp
{
    public class SQLHelper
    {
        //public static string constr { get; set; }

        public static string constr = ConfigurationManager.ConnectionStrings["ConnectionU8"].ToString();
        #region AddReturnNewID
        //public static string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        /// <summary>
        /// 添加数据并返回新ID
        /// </summary>
        /// <param name="safeSql">Insert语句</param>
        /// <returns></returns>
        public static int AddReturnNewID(string safeSql)
        {
            try
            {
                return AddReturnNewID(safeSql, null);
            }
            catch (Exception ex)
            {
                throw ex;
                return 0;
            }
        }



        /// <summary>
        /// 添加并返回新IDed
        /// </summary>
        /// <param name="safeSql">Insert语句</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public static int AddReturnNewID(string safeSql, params SqlParameter[] paras)
        {
            try
            {
                string sql = safeSql + " select SCOPE_IDENTITY()";
                return Convert.ToInt32(ExecuteScalar(sql, paras));
            }
            catch (Exception ex)
            {
                throw ex;
                return 0;
            }
        }

        #endregion AddReturnNewID

        #region ExecuteSql

        /// <summary>
        /// 执行sql语句，返回受影响行数
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        /// <returns></returns>
        public static int ExecuteSql(string safeSql)
        {
            try
            {
                return ExecuteSql(safeSql, null);
            }
            catch (Exception ex)
            {
                throw ex;
                return 0;
            }
        }

        /// <summary>
        /// 执行sql语句，返回受影响行数
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public static int ExecuteSql(string safeSql, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        con.Open();
                        SetCmdParam(con, cmd, CommandType.Text, safeSql, paras);
                        int result = cmd.ExecuteNonQuery();
                        return result;
                    }
                    catch (SqlException ex)
                    {
                        cmd.Dispose();
                        con.Close();
                        throw ex;
                    }
                }
            }
        }

        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] parameters)
        {
            try
            {
                object obj;
                using (SqlConnection conn = new SqlConnection(constr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = cmdType;
                        cmd.CommandText = cmdText;
                        cmd.Parameters.AddRange(parameters);
                        cmd.CommandTimeout = 0;
                        obj = cmd.ExecuteScalar();
                    }
                }
                return obj;
            }
            catch (Exception ex)
            {
                //LogException.WriteLog(ex, cmdText);
                return null;
            }
        }

        #endregion ExecuteSql

        #region ExecuteSqls

        /// <summary>
        /// 批量执行查询语句（包括存储过程），放到DataSet中
        /// </summary>
        /// <param name="SQLStringList">查询语句集合</param>
        /// <param name="LstParas">每条sql对应的参数组</param>
        /// <returns></returns>
        public static DataSet ExecuteSqls(List<String> SQLStringList)
        {
            return ExecuteSqls(SQLStringList, null);
        }

        /// <summary>
        /// 批量执行查询语句（包括存储过程），放到DataSet中
        /// </summary>
        /// <param name="SQLStringList">查询语句集合</param>
        /// <param name="LstParas">每条sql对应的参数组</param>
        /// <returns></returns>
        public static DataSet ExecuteSqls(List<String> SQLStringList, List<SqlParameter[]> LstParas)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                string strsql = "";
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                DataSet ds = new DataSet();
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        strsql = SQLStringList[n];
                        SetCmdParam(con, cmd, CommandType.Text, strsql, null);
                        DataTable dt = new DataTable();
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                        ds.Tables.Add(dt);
                        cmd.Parameters.Clear();
                    }
                    return ds;
                }
                catch (Exception ex)
                {
                    //LogException.WriteLog(ex, strsql);
                    return null;
                }
            }
        }

        #endregion ExecuteSqls

        #region ExecuteSqlTran

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>
        /// <returns>执行结果</returns>
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            return ExecuteSqlTran(SQLStringList, null);
        }
        public static string reesult;
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务,每条sql语句都要有同索引对应的参数，没有参数传NULL。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>
        /// <param name="LstParas">每条sql对应的参数组</param>
        /// <returns>执行结果</returns>
        public static int ExecuteSqlTran(List<String> SQLStringList, List<SqlParameter[]> LstParas)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                SqlTransaction tx = con.BeginTransaction();
                cmd.Transaction = tx;
                string strsql = "";
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        strsql = SQLStringList[n];
                        LogException.WriteiLog("数据库执行语句","SQLtran:" + strsql,false,DBHelp.LogException.ilog);
                        if (strsql.Trim().Length > 1)
                        {
                            if (LstParas == null)
                                SetCmdParam(con, cmd, strsql);
                            else
                                SetCmdParam(con, cmd, strsql, LstParas[n]);
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch (Exception ex)
                {
                    reesult = ex.Message;
                    LogException.WriteTxtLog(ex);//错误信息输出
                    LogException.WriteLog(ex, strsql);//日志
                    tx.Rollback();
                    return 0;
                }
            }
        }


        public static int ExecuteSqlTranas (string constr,List<String> SQLStringList, List<SqlParameter[]> LstParas)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                //DBHelp.LogException.WriteLogts("SQL连接方式:" + constr);
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                SqlTransaction tx = con.BeginTransaction();
                cmd.Transaction = tx;
                string strsql = "";
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        strsql = SQLStringList[n];
                        //LogException.WriteLogts("SQLtran:" + strsql);
                        if (strsql.Trim().Length > 1)
                        {
                            //LogException.WriteLogts("进入length:" + strsql.Trim().Length.ToString());
                            if (LstParas == null)
                                SetCmdParam(con, cmd, strsql);
                            else
                                SetCmdParam(con, cmd, strsql, LstParas[n]);
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch (Exception ex)
                {
                    reesult = ex.Message;
                    LogException.WriteTxtLog(ex);//错误信息输出
                    LogException.WriteLog(ex, strsql);//日志
                    tx.Rollback();
                    return 0;
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>
        /// <param name="LstParas">每条sql对应的参数组</param>
        /// <param name="returnValue">异常信息</param>
        /// <returns>异常信息</returns>
        public static int ExecuteSqlTran(List<String> SQLStringList, ref string returnValue)
        {
            returnValue = string.Empty;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                string strsql = "";
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            SetCmdParam(conn, cmd, strsql);
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch (Exception ex)
                {
                    //LogException.WriteLog(ex, strsql);
                    tx.Rollback();
                    return 0;
                }
            }
        }
        public static string ExecuteSqlTrann(List<String> SQLStringList, ref string returnValue)
        {
            returnValue = string.Empty;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                string strsql = "";
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            SetCmdParam(conn, cmd, strsql);
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                    tx.Commit();
                    return count+"成功"+count+"条";
                }
                catch (Exception ex)
                {
                    LogException.WriteLog(ex, strsql);
                    tx.Rollback();
                    return ex.Message;
                }
            }
        }

        #endregion ExecuteSqlTran

        #region ExecuteScalar

        /// <summary>
        /// 执行sql语句，返回第一行第一列的值
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        /// <returns></returns>
        public static object ExecuteScalar(string safeSql)
        {
            return ExecuteScalar(safeSql, null);
        }

        /// <summary>
        /// 执行sql语句，返回第一行第一列的值
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string safeSql, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        con.Open();
                        SetCmdParam(con, cmd, CommandType.Text, safeSql, paras);
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                            return null;
                        else
                            return obj;
                    }
                    catch (SqlException e)
                    {
                        cmd.Dispose();
                        con.Close();
                        throw e;
                    }
                }
            }
        }

        #endregion ExecuteScalar

        #region ExecuteProcedure

        #region 受影响行数

        /// <summary>
        /// 执行存储过程，返回影响的行数
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int ExecuteProcedure(string storedProcName, out int rowsAffected)
        {
            return ExecuteProcedure(storedProcName, null, out rowsAffected);
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="paras">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int ExecuteProcedure(string storedProcName, SqlParameter[] paras, out int rowsAffected)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                try
                {
                    int result;
                    con.Open();
                    SqlCommand cmd = BuildIntCommand(con, storedProcName, paras);
                    rowsAffected = cmd.ExecuteNonQuery();
                    result = (int)cmd.Parameters["ReturnValue"].Value;
                    return result;
                }
                catch (SqlException ex)
                {
                    //LogException.WriteLog(ex, storedProcName);
                    con.Close();
                    rowsAffected = 0;
                    return 0;
                }
            }
        }

        #endregion 受影响行数

        #region 返回查询数据表

        /// <summary>
        /// 执行sql语句，返回查询数据
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static DataTable ExecuteProcedure(string storedProcName)
        {
            return ExecuteProcedure(storedProcName, null);
        }

        /// <summary>
        /// 执行sql语句，返回查询数据
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExecuteProcedure(string storedProcName, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                try
                {
                    DataTable dt = new DataTable();
                    con.Open();
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = BuildQueryCommand(con, storedProcName, paras);
                    sda.SelectCommand.CommandTimeout = 0;
                    sda.Fill(dt);
                    con.Close();
                    return dt;
                }
                catch (SqlException ex)
                {
                    //LogException.WriteLog(ex, storedProcName);
                    con.Close();
                    return null;
                }
            }
        }

        #endregion 返回查询数据表

        #endregion ExecuteProcedure

        #region GetDataTable

        //创建连接对象
        private static SqlConnection con;


        /// <summary>
        /// 通过SQL语句查询多条数据库信息
        /// </summary>
        /// <returns>DataTable对象</returns>
        public static DataTable GetDataTable(string safeSql)
        {
            try
            {
                return GetDataTable(safeSql, null);
            }
            catch (SqlException ex)
            {
                LogException.WriteTxtEog(ex);
                con.Close();
                return null;
            }
        }

        /// <summary>
        /// 通过SQL语句查询多条数据库信息
        /// </summary>
        /// <returns>DataTable对象</returns>
        public static string GetString(string safeSql)
        {
            try
            {
                DataTable dt = GetDataTable(safeSql, null);
                if (dt.Rows.Count == 0)
                    return "";
                else
                    return dt.Rows[0][0].ToString();
            }
            catch (SqlException ex)
            {
                DBHelp.LogException.WriteiLog("数据库异常",ex.Message,false,true);
                con.Close();
                return "";
            }
        }

        /// <summary>
        /// 通过SQL语句查询多条数据库信息
        /// </summary>
        /// <returns>DataTable对象</returns>
        public static DataTable GetDataTable(string safeSql, params SqlParameter[] paras)
        {
            try
            {
                return GetDataTable(safeSql, CommandType.Text, paras);
            }
            catch (SqlException ex)
            {
                DBHelp.LogException.WriteiLog("数据库异常","getdate:"+ex.Message,true,true);
                con.Close();
                return null;
            }
        }

        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sqlStr"></param>
        public static int GetExecute(string sqlStr)
        {
            InitConnection();
            SqlCommand cmd = new SqlCommand(sqlStr, con);
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result;
        }

        /// <summary>
        /// 查询，获取DataReader
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(string sqlStr)
        {
            InitConnection();
            //创建命令对象
            SqlCommand cmd = new SqlCommand(sqlStr, con);
            //当DataReader对象关闭时，将自动关闭连接对象
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 获取DataTable数据
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="cmdType">SQL类型</param>
        /// <param name="paras">参数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTable(string cmdText, CommandType cmdType, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        con.Open();
                        SetCmdParam(con, cmd, cmdType, cmdText, paras);
                        DataTable dt = new DataTable();
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                        return dt;
                    }
                    catch (SqlException ex)
                    {
                        LogException.WriteTxtEog(ex);
                        LogException.WriteLog(ex, cmdText);
                        cmd.Dispose();
                        con.Close();
                        return null;
                    }
                }
            }
        }

        /// <summary>
        /// 获取DataSet数据
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="cmdType">SQL类型</param>
        /// <param name="paras">参数</param>
        /// <returns>DataTable</returns>
        public static DataSet GetDataSet(string cmdText, CommandType cmdType, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        con.Open();
                        SetCmdParam(con, cmd, cmdType, cmdText, paras);
                        DataSet ds = new DataSet();
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(ds);
                        return ds;
                    }
                    catch (SqlException ex)
                    {
                        //LogException.WriteLog(ex, cmdText);
                        cmd.Dispose();
                        con.Close();
                        return null;
                    }
                }
            }
        }

        internal static int ExecuteSqlTranas(string constrs, List<string> lis)
        {
            throw new NotImplementedException();
        }

        //初始化数据连接
        private static SqlConnection InitConnection()
        {
            if (con == null)
            {
                con = new SqlConnection(constr);
                con.Open();
            }
            else if (con.State == System.Data.ConnectionState.Closed)
            {
                con.Open();
            }
            else if (con.State == System.Data.ConnectionState.Broken)
            {
                con.Close();
                con.Open();
            }
            return con;
        }

        #endregion GetDataTable

        #region 构建cmd参数

        #region SetCmdParam

        /// <summary>
        /// 构建cmd参数
        /// </summary>
        /// <param name="con"></param>
        /// <param name="cmd"></param>
        /// <param name="cmdText">文本</param>
        /// <param name="paras">参数</param>
        private static void SetCmdParam(SqlConnection con, SqlCommand cmd, string cmdText)
        {
            SetCmdParam(con, cmd, CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 构建cmd参数
        /// </summary>
        /// <param name="con"></param>
        /// <param name="cmd"></param>
        /// <param name="cmdText">文本</param>
        /// <param name="paras">参数</param>
        private static void SetCmdParam(SqlConnection con, SqlCommand cmd, string cmdText, SqlParameter[] paras)
        {
            SetCmdParam(con, cmd, CommandType.Text, cmdText, paras);
        }

        /// <summary>
        /// 构建cmd参数
        /// </summary>
        /// <param name="con"></param>
        /// <param name="cmd"></param>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">文本</param>
        /// <param name="paras">参数</param>
        private static void SetCmdParam(SqlConnection con, SqlCommand cmd, CommandType cmdType, string cmdText, params SqlParameter[] paras)
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
            cmd.Connection = con;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (paras == null)
                return;
            foreach (SqlParameter para in paras)
            {
                if ((para.Direction == ParameterDirection.InputOutput || para.Direction == ParameterDirection.Input) && para.Value == DBNull.Value)
                    para.Value = DBNull.Value;
                cmd.Parameters.Add(para);
            }
        }

        #endregion SetCmdParam

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="dic">输出参数列表,其中key=输出参数名称</param>
        /// <returns>dataset</returns>
        public static DataTable RunProcedure(string storedProcName, IDataParameter[] parameters, ref Dictionary<string, string> dic)
        {
            DataTable dt = new DataTable();
            using (SqlConnection connection = new SqlConnection(constr))
            {
                //设置参数
                SqlCommand cmd = BuildQueryCommand(connection, storedProcName, parameters);
                //获取数据
                SqlDataAdapter adapter = new SqlDataAdapter
                {
                    SelectCommand = cmd
                };
                adapter.SelectCommand.CommandTimeout = 0;
                adapter.Fill(dt);

                //关闭连接
                connection.Close();

                //给输出参数列表赋值
                Dictionary<string, string> dicnew = new Dictionary<string, string>();
                foreach (KeyValuePair<string, string> item in dic)
                {
                    try
                    {
                        dicnew.Add(item.Key, cmd.Parameters[item.Key].Value.ToString());
                    }
                    catch (Exception) { }
                }
                //返回
                dic = dicnew;
                return dt;
            }
        }

        #region BuildIntCommand

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="con">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="paras">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection con, string storedProcName, IDataParameter[] paras)
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
            SqlCommand cmd = new SqlCommand(storedProcName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            if (paras == null || paras.Length == 0)
                return cmd;
            foreach (SqlParameter para in paras)
            {
                if (para != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((para.Direction == ParameterDirection.InputOutput || para.Direction == ParameterDirection.Input) &&
                        (para.Value == null))
                    {
                        para.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(para);
                }
            }
            return cmd;
        }

        #endregion BuildIntCommand

        #endregion 构建cmd参数

        #region 通过SQL返回DATASET

        /// <summary>
        /// 通过SQL返回DATASET
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="connectionString">数据库连接语句</param>
        /// <param name="result">返回的DATASET</param>
        /// <param name="errMsg">出错信息</param>
        /// <returns>0：正确 非0：错误</returns>
        static public int GetDatasetBy(string sqlString, string connectionString, out DataTable dts, out string errMsg)
        {
            errMsg = "";
            dts = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection();
            SqlDataAdapter adp = new SqlDataAdapter();
            conn.ConnectionString = connectionString;
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
                return -1;
            }
            cmd.Connection = conn;
            cmd.CommandText = sqlString;
            adp.SelectCommand = cmd;
            try
            {
                adp.Fill(dts);
                return 0;
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
                return -2;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn = null;
                }
            }
        }

        static public int GetDataset(string sqlString, string connectionString, out DataSet result, out string errMsg)
        {
            errMsg = "";
            result = new DataSet();
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection();
            SqlDataAdapter adp = new SqlDataAdapter();
            conn.ConnectionString = connectionString;
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
                return -1;
            }
            cmd.Connection = conn;
            cmd.CommandText = sqlString;
            adp.SelectCommand = cmd;
            try
            {
                adp.Fill(result);
                return 0;
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
                return -2;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn = null;
                }
            }
        }

        #endregion 通过SQL返回DATASET

        public static int ExecuteNonQuery(string cmdText)
        {
            try
            {
                int execnum = 0;
                using (SqlConnection conn = new SqlConnection(constr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = cmdText;
                        execnum = cmd.ExecuteNonQuery();
                    }
                }
                return execnum;
            }
            catch (Exception ex)
            {
                //LogException.WriteLog(ex, cmdText);
                //cmd.Dispose();
                con.Close();
                return 0;
            }
        }

        public static int ExecuteNonQuery(string strsql, SqlParameter[] para)
        {
            SqlConnection conn = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand(strsql, conn);
            conn.Open();
            if (para != null)
            {
                cmd.Parameters.AddRange(para);//防注入式攻击
            }
            int x = 0;
            try
            {
                x = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //LogException.WriteLog(ex, strsql);
            }
            finally
            {
                conn.Close();
            }
            return x;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static int SpExecuteNonQuery(string cmdText)
        {
            try
            {
                int execnum = 0;
                using (SqlConnection conn = new SqlConnection(constr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = cmdText;
                        cmd.CommandType = CommandType.StoredProcedure;
                        execnum = cmd.ExecuteNonQuery();
                    }
                }
                return execnum;
            }
            catch (Exception ex)
            {
                //LogException.WriteLog(ex, cmdText);
                //cmd.Dispose();
                con.Close();
                return 0;
            }
        }

        //查询数据是否存在
        public static bool Exists(string cmdText, params SqlParameter[] paras)
        {
            try
            {
                DataTable dt = GetDataTable(cmdText, paras);
                if (Convert.ToInt32(dt.Rows[0][0].ToString()) == 0)
                    return false;
                else
                    return true;
            }
            catch (Exception ex)
            {
                //LogException.WriteLog(ex, cmdText);
                con.Close();
                return false;
            }
        }

        /// <summary>
        /// </summary>
        /// <param name="connectionString">目标连接字符</param>
        /// <param name="TableName">目标表</param>
        /// <param name="dt">源数据</param>
        public static void SqlBulkCopyByDatatable(string TableName, DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(constr, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(dt);
                    }
                    catch (System.Exception ex)
                    {
                        //LogException.WriteLog(ex, TableName);
                    }
                }
            }
        }

    }
}
